﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;
using Microsoft.VisualBasic.FileIO;
using System.Data.SQLite;

namespace ABDLight
{
    public class CleanBackUp
    {
        public string CleanID { get; set; }
        public int RowGUID { get; set; }
        public string Login { get; set; }
        public string FileName { get; set; }
        public string iqPostCode { get; set; }
        public string iqFullAddress { get; set; }
        public string iqHouse { get; set; }
        public string iqCase { get; set; }
        public string iqBuild { get; set; }
        public string iqLiter { get; set; }
        public string iqVlad { get; set; }
        public string iqBlock { get; set; }
        public string iqPom { get; set; }
        public string iqPav { get; set; }
        public string iqUch { get; set; }
        public string iqObsch { get; set; }
        public string iqFlat { get; set; }
        public string iqKom { get; set; }
        public string iqOffice { get; set; }
        public string iqKab { get; set; }
        public string iqAbon { get; set; }
        public string iqVch { get; set; }
        public string iqKLADR { get; set; }
        public string iqError { get; set; }
        public string iqStatus { get; set; }
        public string iqCountry { get; set; }
        public string iqRegion { get; set; }
        public string iqRegionType { get; set; }
        public string iqSubRegion { get; set; }
        public string iqSubRegionType { get; set; }
        public string iqCity { get; set; }
        public string iqCityType { get; set; }
        public string iqNp { get; set; }
        public string iqNpType { get; set; }
        public string iqStreet { get; set; }
        public string iqStreetType { get; set; }
        public string iqWorkAddress { get; set; }
        public string ISOK { get; set; }
        public List<CleanBackUp> CleanBackUpList { get; set; }

        public CleanBackUp()
        {
            Login = "";
            FileName = "";
            iqPostCode = "";
            iqFullAddress = "";
            iqHouse = "";
            iqCase = "";
            iqBuild = "";
            iqLiter = "";
            iqVlad = "";
            iqBlock = "";
            iqPom = "";
            iqPav = "";
            iqUch = "";
            iqObsch = "";
            iqFlat = "";
            iqKom = "";
            iqOffice = "";
            iqKab = "";
            iqAbon = "";
            iqVch = "";
            iqKLADR = "";
            iqError = "";
            iqStatus = "";
            iqCountry = "";
            iqRegion = "";
            iqRegionType = "";
            iqSubRegion = "";
            iqSubRegionType = "";
            iqCity = "";
            iqCityType = "";
            iqNp = "";
            iqNpType = "";
            iqStreet = "";
            iqStreetType = "";
            iqWorkAddress = "";
            ISOK = "";   
            CleanBackUpList = new List<CleanBackUp>();
        }


        /// <summary>
        /// CleanBackUp GetCleanRow(int RowGUID, string FileName="", string Login="")
        /// </summary>
        /// <param name="RowGUID"></param>
        /// <param name="FileName"></param>
        /// <param name="Login"></param>
        /// <returns></returns>
        public static CleanBackUp GetCleanRow(int RowGUID, string FileName="", string Login="")
        {
            CleanBackUp elem = new CleanBackUp();


            if (!string.IsNullOrEmpty(RowGUID.ToString()))
            {
                using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ABDLightDBConnectionString))
                {
                    conn.Open();

                    try
                    {
                        SqlDataReader reader;
                        SqlCommand command = new SqlCommand(

                            "SELECT " +
                                "[c_CleanID]" +
                                ",[c_Login]" +
                                ",[c_FileName]" +
                                ",[c_iqPostCode]" +
                                ",[c_iqFullAddress]" +
                                ",[c_iqHouse]" +
                                ",[c_iqCase]" +
                                ",[c_iqBuild]" +
                                ",[c_iqLiter]" +
                                ",[c_iqVlad]" +
                                ",[c_iqBlock]" +
                                ",[c_iqPom]" +
                                ",[c_iqPav]" +
                                ",[c_iqUch]" +
                                ",[c_iqObsch]" +
                                ",[c_iqFlat]" +
                                ",[c_iqKom]" +
                                ",[c_iqOffice]" +
                                ",[c_iqKab]" +
                                ",[c_iqAbon]" +
                                ",[c_iqVch]" +
                                ",[c_iqKLADR]" +
                                ",[c_iqError]" +
                                ",[c_iqStatus]" +
                                ",[c_iqCountry]" +
                                ",[c_iqRegion]" +
                                ",[c_iqRegionType]" +
                                ",[c_iqSubRegion]" +
                                ",[c_iqSubRegionType]" +
                                ",[c_iqCity]" +
                                ",[c_iqCityType]" +
                                ",[c_iqNp]" +
                                ",[c_iqNpType]" +
                                ",[c_iqStreet]" +
                                ",[c_iqStreetType]" +
                                ",[c_iqWorkAddress]" +
                                ",[c_ISOK]" +
                            "FROM [CleanBackUp] WHERE [c_RowGUID]=" + RowGUID, conn);

                        reader = command.ExecuteReader();

                        if (reader.Read())
                        {
                            if (!reader.IsDBNull(0))
                            {
                                elem.CleanID = (reader.GetSqlGuid(0).ToString());
                            }

                            if (!reader.IsDBNull(1))
                            {
                                elem.Login = (reader.GetString(1));

                            }

                            if (!reader.IsDBNull(2))
                            {
                                elem.FileName = (reader.GetString(2));
                            }
                            if (!reader.IsDBNull(3))
                            {
                                elem.iqPostCode = (reader.GetString(3));
                            }
                            if (!reader.IsDBNull(4))
                            {
                                elem.iqFullAddress = (reader.GetString(4));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(5))
                            {
                                elem.iqHouse = (reader.GetString(5));
                            }

                            if (!reader.IsDBNull(6))
                            {
                                elem.iqCase = (reader.GetString(6));

                            }

                            if (!reader.IsDBNull(7))
                            {
                                elem.iqBuild = (reader.GetString(7));
                            }
                            if (!reader.IsDBNull(8))
                            {
                                elem.iqLiter = (reader.GetString(8));
                            }
                            if (!reader.IsDBNull(9))
                            {
                                elem.iqVlad = (reader.GetString(9));
                            }
                            ///////////////////
                            if (!reader.IsDBNull(10))
                            {
                                elem.iqBlock = (reader.GetString(10));
                            }

                            if (!reader.IsDBNull(11))
                            {
                                elem.iqPom = (reader.GetString(11));

                            }

                            if (!reader.IsDBNull(12))
                            {
                                elem.iqPav = (reader.GetString(12));
                            }
                            if (!reader.IsDBNull(13))
                            {
                                elem.iqUch = (reader.GetString(13));
                            }
                            if (!reader.IsDBNull(14))
                            {
                                elem.iqObsch = (reader.GetString(14));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(15))
                            {
                                elem.iqFlat = (reader.GetString(15));
                            }

                            if (!reader.IsDBNull(16))
                            {
                                elem.iqKom = (reader.GetString(16));

                            }

                            if (!reader.IsDBNull(17))
                            {
                                elem.iqOffice = (reader.GetString(17));
                            }
                            if (!reader.IsDBNull(18))
                            {
                                elem.iqKab = (reader.GetString(18));
                            }
                            if (!reader.IsDBNull(19))
                            {
                                elem.iqAbon = (reader.GetString(19));
                            }
                            //////////////////////////////////////////////
                            if (!reader.IsDBNull(20))
                            {
                                elem.iqVch = (reader.GetString(20));
                            }

                            if (!reader.IsDBNull(21))
                            {
                                elem.iqKLADR = (reader.GetString(21));

                            }

                            if (!reader.IsDBNull(22))
                            {
                                elem.iqError = (reader.GetString(22));
                            }
                            if (!reader.IsDBNull(23))
                            {
                                elem.iqStatus = (reader.GetString(23));
                            }
                            if (!reader.IsDBNull(24))
                            {
                                elem.iqCountry = (reader.GetString(24));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(25))
                            {
                                elem.iqRegion = (reader.GetString(25));
                            }

                            if (!reader.IsDBNull(26))
                            {
                                elem.iqRegionType = (reader.GetString(26));

                            }

                            if (!reader.IsDBNull(27))
                            {
                                elem.iqSubRegion = (reader.GetString(27));
                            }
                            if (!reader.IsDBNull(28))
                            {
                                elem.iqSubRegionType = (reader.GetString(28));
                            }
                            if (!reader.IsDBNull(29))
                            {
                                elem.iqCity = (reader.GetString(29));
                            }

                            if (!reader.IsDBNull(30))
                            {
                                elem.iqCityType = (reader.GetString(30));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(31))
                            {
                                elem.iqNp = (reader.GetString(31));
                            }

                            if (!reader.IsDBNull(32))
                            {
                                elem.iqNpType = (reader.GetString(32));

                            }

                            if (!reader.IsDBNull(33))
                            {
                                elem.iqStreet = (reader.GetString(33));
                            }
                            if (!reader.IsDBNull(34))
                            {
                                elem.iqStreetType = (reader.GetString(34));
                            }
                            if (!reader.IsDBNull(35))
                            {
                                elem.iqWorkAddress = (reader.GetString(35));
                            }

                            if (!reader.IsDBNull(36))
                            {
                                elem.ISOK = (reader.GetString(36));
                            }
                        }
                        reader.Close();
                    }


                    finally
                    {

                    }
                }
            }

            return elem;
        
        }

        /// <summary>
        /// GetCleanBackUp(string FileName = "", string Login = "")
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="Login"></param>
        /// <returns></returns>
        public static  CleanBackUp GetCleanBackUp(string OrderBy = "c_RowGUID" , string FileName = "", string Login = "")
        {
            CleanBackUp elem = new CleanBackUp();
            
            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ABDLightDBConnectionString))
                {
                    conn.Open();

                    try
                    {
                        SqlDataReader reader;
                        SqlCommand command = new SqlCommand(

                            "SELECT " +
                                "[c_CleanID]" +
                                ",[c_Login]" +
                                ",[c_FileName]" +
                                ",[c_iqPostCode]" +
                                ",[c_iqFullAddress]" +
                                ",[c_iqHouse]" +
                                ",[c_iqCase]" +
                                ",[c_iqBuild]" +
                                ",[c_iqLiter]" +
                                ",[c_iqVlad]" +
                                ",[c_iqBlock]" +
                                ",[c_iqPom]" +
                                ",[c_iqPav]" +
                                ",[c_iqUch]" +
                                ",[c_iqObsch]" +
                                ",[c_iqFlat]" +
                                ",[c_iqKom]" +
                                ",[c_iqOffice]" +
                                ",[c_iqKab]" +
                                ",[c_iqAbon]" +
                                ",[c_iqVch]" +
                                ",[c_iqKLADR]" +
                                ",[c_iqError]" +
                                ",[c_iqStatus]" +
                                ",[c_iqCountry]" +
                                ",[c_iqRegion]" +
                                ",[c_iqRegionType]" +
                                ",[c_iqSubRegion]" +
                                ",[c_iqSubRegionType]" +
                                ",[c_iqCity]" +
                                ",[c_iqCityType]" +
                                ",[c_iqNp]" +
                                ",[c_iqNpType]" +
                                ",[c_iqStreet]" +
                                ",[c_iqStreetType]" +
                                ",[c_iqWorkAddress]" +
                                ",[c_RowGUID]" +
                                ",[c_ISOK]" +
                            "FROM [CleanBackUp] ORDER BY " + OrderBy , conn);

                        reader = command.ExecuteReader();

                        while (reader.Read())
                        {
                            if (!reader.IsDBNull(0))
                            {
                                elem.CleanID = (reader.GetSqlGuid(0).ToString());
                            }

                            if (!reader.IsDBNull(1))
                            {
                                elem.Login = (reader.GetString(1));

                            }

                            if (!reader.IsDBNull(2))
                            {
                                elem.FileName = (reader.GetString(2));
                            }
                            if (!reader.IsDBNull(3))
                            {
                                elem.iqPostCode = (reader.GetString(3));
                            }
                            if (!reader.IsDBNull(4))
                            {
                                elem.iqFullAddress = (reader.GetString(4));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(5))
                            {
                                elem.iqHouse = (reader.GetString(5));
                            }

                            if (!reader.IsDBNull(6))
                            {
                                elem.iqCase = (reader.GetString(6));

                            }

                            if (!reader.IsDBNull(7))
                            {
                                elem.iqBuild = (reader.GetString(7));
                            }
                            if (!reader.IsDBNull(8))
                            {
                                elem.iqLiter = (reader.GetString(8));
                            }
                            if (!reader.IsDBNull(9))
                            {
                                elem.iqVlad = (reader.GetString(9));
                            }
                            ///////////////////
                            if (!reader.IsDBNull(10))
                            {
                                elem.iqBlock = (reader.GetString(10));
                            }

                            if (!reader.IsDBNull(11))
                            {
                                elem.iqPom = (reader.GetString(11));

                            }

                            if (!reader.IsDBNull(12))
                            {
                                elem.iqPav = (reader.GetString(12));
                            }
                            if (!reader.IsDBNull(13))
                            {
                                elem.iqUch = (reader.GetString(13));
                            }
                            if (!reader.IsDBNull(14))
                            {
                                elem.iqObsch = (reader.GetString(14));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(15))
                            {
                                elem.iqFlat = (reader.GetString(15));
                            }

                            if (!reader.IsDBNull(16))
                            {
                                elem.iqKom = (reader.GetString(16));

                            }

                            if (!reader.IsDBNull(17))
                            {
                                elem.iqOffice = (reader.GetString(17));
                            }
                            if (!reader.IsDBNull(18))
                            {
                                elem.iqKab = (reader.GetString(18));
                            }
                            if (!reader.IsDBNull(19))
                            {
                                elem.iqAbon = (reader.GetString(19));
                            }
                            //////////////////////////////////////////////
                            if (!reader.IsDBNull(20))
                            {
                                elem.iqVch = (reader.GetString(20));
                            }

                            if (!reader.IsDBNull(21))
                            {
                                elem.iqKLADR = (reader.GetString(21));

                            }

                            if (!reader.IsDBNull(22))
                            {
                                elem.iqError = (reader.GetString(22));
                            }
                            if (!reader.IsDBNull(23))
                            {
                                elem.iqStatus = (reader.GetString(23));
                            }
                            if (!reader.IsDBNull(24))
                            {
                                elem.iqCountry = (reader.GetString(24));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(25))
                            {
                                elem.iqRegion = (reader.GetString(25));
                            }

                            if (!reader.IsDBNull(26))
                            {
                                elem.iqRegionType = (reader.GetString(26));

                            }

                            if (!reader.IsDBNull(27))
                            {
                                elem.iqSubRegion = (reader.GetString(27));
                            }
                            if (!reader.IsDBNull(28))
                            {
                                elem.iqSubRegionType = (reader.GetString(28));
                            }
                            if (!reader.IsDBNull(29))
                            {
                                elem.iqCity = (reader.GetString(29));
                            }

                            if (!reader.IsDBNull(30))
                            {
                                elem.iqCityType = (reader.GetString(30));
                            }
                            /////////////////////////////////////////////
                            if (!reader.IsDBNull(31))
                            {
                                elem.iqNp = (reader.GetString(31));
                            }

                            if (!reader.IsDBNull(32))
                            {
                                elem.iqNpType = (reader.GetString(32));

                            }

                            if (!reader.IsDBNull(33))
                            {
                                elem.iqStreet = (reader.GetString(33));
                            }
                            if (!reader.IsDBNull(34))
                            {
                                elem.iqStreetType = (reader.GetString(34));
                            }
                            if (!reader.IsDBNull(35))
                            {
                                elem.iqWorkAddress = (reader.GetString(35));
                            }

                            if (!reader.IsDBNull(36))
                            {
                                elem.RowGUID = (reader.GetInt32(36));
                            }

                            if (!reader.IsDBNull(37))
                            {
                                elem.ISOK = (reader.GetString(37));
                            }
                            elem.CleanBackUpList.Add(elem);
                        }
                        reader.Close();
                    }


                    finally
                    {

                    }
                }


            return elem;

        }

        /// <summary>
        /// AddCleanRow(CleanBackUp item)
        /// </summary>
        /// <param name="item"></param>
        public  void AddCleanRow(CleanBackUp item)
        {

            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ABDLightDBConnectionString))
            {
                conn.Open();
                try
                {
                    string SQLCommand = "INSERT INTO CleanBackUp" +

                    "([c_Login]" +
                    ",[c_FileName]" +
                    ",[c_iqPostCode]" +
                    ",[c_iqFullAddress]" +
                    ",[c_iqHouse]" +
                    ",[c_iqCase]" +
                    ",[c_iqBuild]" +
                    ",[c_iqLiter]" +
                    ",[c_iqVlad]" +
                    ",[c_iqBlock]" +
                    ",[c_iqPom]" +
                    ",[c_iqPav]" +
                    ",[c_iqUch]" +
                    ",[c_iqObsch]" +
                    ",[c_iqFlat]" +
                    ",[c_iqKom]" +
                    ",[c_iqOffice]" +
                    ",[c_iqKab]" +
                    ",[c_iqAbon]" +
                    ",[c_iqVch]" +
                    ",[c_iqKLADR]" +
                    ",[c_iqError]" +
                    ",[c_iqStatus]" +
                    ",[c_iqCountry]" +
                    ",[c_iqRegion]" +
                    ",[c_iqRegionType]" +
                    ",[c_iqSubRegion]" +
                    ",[c_iqSubRegionType]" +
                    ",[c_iqCity]" +
                    ",[c_iqCityType]" +
                    ",[c_iqNp]" +
                    ",[c_iqNpType]" +
                    ",[c_iqStreet]" +
                    ",[c_iqStreetType]" +
                    ",[c_iqWorkAddress]" +
                    ",[c_ISOK])" +
                "VALUES" +

                    "('" + ABDLight.Properties.Settings.Default.Login + "'," +
                    "'" + frmClean.cCfg.LastFileName + "'," +
                    "'" + item.iqPostCode + "'," +
                    "'" + item.iqFullAddress + "'," +
                    "'" + item.iqHouse + "'," +
                    "'" + item.iqCase + "'," +
                    "'" + item.iqBuild + "'," +
                    "'" + item.iqLiter + "'," +
                    "'" + item.iqVlad + "'," +
                    "'" + item.iqBlock + "'," +
                    "'" + item.iqPom + "'," +
                    "'" + item.iqPav + "'," +
                    "'" + item.iqUch + "'," +
                    "'" + item.iqObsch + "'," +
                    "'" + item.iqFlat + "'," +
                    "'" + item.iqKom + "'," +
                    "'" + item.iqOffice + "'," +
                    "'" + item.iqKab + "'," +
                    "'" + item.iqAbon + "'," +
                    "'" + item.iqVch + "'," +
                    "'" + item.iqKLADR + "'," +
                    "'" + item.iqError + "'," +
                    "'" + item.iqStatus + "'," +
                    "'" + item.iqCountry + "'," +
                    "'" + item.iqRegion + "'," +
                    "'" + item.iqRegionType + "'," +
                    "'" + item.iqSubRegion + "'," +
                    "'" + "" + "'," +
                    "'" + item.iqCity + "'," +
                    "'" + item.iqCityType + "'," +
                    "'" + item.iqNp + "'," +
                    "'" + item.iqNpType + "'," +
                    "'" + item.iqStreet + "'," +
                    "'" + item.iqStreetType + "'," +
                    "'" + item.iqWorkAddress + "'," +
                    "'" + item.ISOK + "')";

                    SqlCommand command = new SqlCommand(SQLCommand, conn);
                    command.ExecuteNonQuery();
                }

                finally
                {
                    // Always call Close when done reading.

                }
            }
        }

        /// <summary>
        /// UpdateCleanRow(CleanBackUp item)
        /// </summary>
        /// <param name="item"></param>
        public  void UpdateCleanRow(CleanBackUp item)
        {
            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ABDLightDBConnectionString))
            {
                conn.Open();
                try
                {

                    string SQLCommand = "update dbo.CleanBackUp set" +
                    "[c_Login]=" + "'" + ABDLight.Properties.Settings.Default.Login + "'," +
                    "[c_FileName]=" + "'" + frmClean.cCfg.LastFileName + "'," +
                    "[c_iqPostCode]=" + "'" + item.iqPostCode + "'," +
                    "[c_iqFullAddress]=" + "'" + item.iqFullAddress + "'" +
                     " where c_RowGUID=" + item.RowGUID;
                    SqlCommand command = new SqlCommand(SQLCommand, conn);
                    command.ExecuteNonQuery();

                    string SQLCommand2 = "update dbo.CleanBackUp set" +
                    "[c_iqHouse]=" + "'" + item.iqHouse + "'," +
                    "[c_iqCase]=" + "'" + item.iqCase + "'," +
                    "[c_iqBuild]=" + "'" + item.iqBuild + "'," +
                    "[c_iqLiter]=" + "'" + item.iqLiter + "'," +
                    "[c_iqVlad]=" + "'" + item.iqVlad + "'," +
                    "[c_iqBlock]=" + "'" + item.iqBlock + "'," +
                    "[c_iqPom]=" + "'" + item.iqPom + "'," +
                    "[c_iqPav]=" + "'" + item.iqPav + "'," +
                    "[c_iqUch]=" + "'" + item.iqUch + "'," +
                    "[c_iqObsch]=" + "'" + item.iqObsch + "'," +
                    "[c_iqFlat]=" + "'" + item.iqFlat + "'," +
                    "[c_iqKom]=" + "'" + item.iqKom + "'," +
                    "[c_iqOffice]=" + "'" + item.iqOffice + "'," +
                    "[c_iqKab]=" + "'" + item.iqKab + "'," +
                    "[c_iqAbon]=" + "'" + item.iqAbon + "'," +
                    "[c_iqVch]=" + "'" + item.iqVch + "'," +
                    "[c_iqKLADR]=" + "'" + item.iqKLADR + "'," +
                    "[c_iqError]=" + "'" + item.iqError + "'," +
                    "[c_iqStatus]=" + "'" + item.iqStatus + "'" +
                    " where c_RowGUID=" + item.RowGUID;
                    SqlCommand command2 = new SqlCommand(SQLCommand2, conn);
                    command2.ExecuteNonQuery();

                    string SQLCommand3 = "update dbo.CleanBackUp set" +
                    "[c_iqCountry]=" + "'" + item.iqCountry + "'," +
                    "[c_iqRegion]=" + "'" + item.iqRegion + "'," +
                    "[c_iqRegionType]=" + "'" + item.iqRegionType + "'," +
                    "[c_iqSubRegion]=" + "'" + item.iqSubRegion + "'," +
                    "[c_iqCity]=" + "'" + item.iqCity + "'," +
                    "[c_iqCityType]=" + "'" + item.iqCityType + "'," +
                    "[c_iqNp]=" + "'" + item.iqNp + "'," +
                    "[c_iqNpType]=" + "'" + item.iqNpType + "'," +
                    "[c_iqStreet]=" + "'" + item.iqStreet + "'," +
                    "[c_iqStreetType]=" + "'" + item.iqStreetType + "'," +
                    "[c_iqWorkAddress]=" + "'" + item.iqWorkAddress + "'," +
                    "[c_ISOK]=" + "'" + item.ISOK + "'" +
                   " where c_RowGUID=" + item.RowGUID;
                    SqlCommand command3 = new SqlCommand(SQLCommand3, conn);
                    command3.ExecuteNonQuery();
                }

                finally
                {
                    // Always call Close when done reading.

                }
            }
        }
    ///////////
        public static void CreateTableIQDQ(string CS = "Server=localhost;Integrated Security=SSPI;", string DB = "ABDLight", string Table = "CleanBackUp")
        {
            using (SqlConnection conn = new SqlConnection(CS + " Initial Catalog=" + DB + ";"))
            {
                conn.Open();
                try
                {

                    //string SQLCommand = "DROP TABLE [" + Table + "]";                    
                    //SqlCommand command = new SqlCommand(SQLCommand, conn);
                    //command.ExecuteNonQuery();

                    string SQLCommand1 = "SET ANSI_NULLS ON";
                    SqlCommand command1 = new SqlCommand(SQLCommand1, conn);
                    command1.ExecuteNonQuery();

                    string SQLCommand5 = "SET QUOTED_IDENTIFIER ON";
                        
                    SqlCommand command5 = new SqlCommand(SQLCommand5, conn);
                    command5.ExecuteNonQuery();

                    string SQLCommand2 = "CREATE TABLE [" + Table + "](" +
                    "[c_CleanID] [uniqueidentifier] ROWGUIDCOL  NOT NULL," +
                    "[c_Login] [nvarchar](50) NOT NULL," +
                    "[c_FileName] [nvarchar](max) NOT NULL," +
                    "[c_iqPostCode] [nvarchar](50) NULL," +
                    "[c_iqFullAddress] [nvarchar](max) NULL," +
                    "[c_iqHouse] [nvarchar](10) NULL," +
                    "[c_iqCase] [nvarchar](10) NULL," +
                    "[c_iqBuild] [nvarchar](10) NULL," +
                    "[c_iqLiter] [nvarchar](10) NULL," +
                    "[c_iqVlad] [nvarchar](10) NULL," +
                    "[c_iqBlock] [nvarchar](10) NULL," +
                    "[c_iqPom] [nvarchar](10) NULL," +
                    "[c_iqPav] [nvarchar](10) NULL," +
                    "[c_iqUch] [nvarchar](10) NULL," +
                    "[c_iqObsch] [nvarchar](10) NULL," +
                    "[c_iqFlat] [nvarchar](10) NULL," +
                    "[c_iqKom] [nvarchar](10) NULL," +
                    "[c_iqOffice] [nvarchar](10) NULL," +
                    "[c_iqKab] [nvarchar](10) NULL," +
                    "[c_iqAbon] [nvarchar](10) NULL," +
                    "[c_iqVch] [nvarchar](50) NULL," +
                    "[c_iqKLADR] [nvarchar](50) NULL," +
                    "[c_iqError] [nvarchar](50) NULL," +
                    "[c_iqStatus] [nvarchar](max) NULL," +
                    "[c_iqCountry] [nvarchar](max) NULL," +
                    "[c_iqRegion] [nvarchar](max) NULL," +
                    "[c_iqRegionType] [nvarchar](max) NULL," +
                    "[c_iqSubRegion] [nvarchar](max) NULL," +
                    "[c_iqSubRegionType] [nvarchar](max) NULL," +
                    "[c_iqCity] [nvarchar](max) NULL," +
                    "[c_iqCityType] [nvarchar](max) NULL," +
                    "[c_iqNp] [nvarchar](max) NULL," +
                    "[c_iqNpType] [nvarchar](max) NULL," +
                    "[c_iqStreet] [nvarchar](max) NULL," +
                    "[c_iqStreetType] [nvarchar](max) NULL," +
                    "[c_iqWorkAddress] [nvarchar](max) NULL," +
                    "[c_RowGUID] [int] IDENTITY(1,1) NOT NULL," +
                    "[c_ISOK] [nvarchar](50) NULL," +
                 "CONSTRAINT [PK_" + Table + "] PRIMARY KEY CLUSTERED" +
                "(" +
                    "[c_CleanID] ASC" +
                ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" +
                ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";

                    SqlCommand command2 = new SqlCommand(SQLCommand2, conn);
                    command2.ExecuteNonQuery();

                    string SQLCommand3 = "ALTER TABLE [" + Table + "] SET (LOCK_ESCALATION = AUTO)";
                    SqlCommand command3 = new SqlCommand(SQLCommand3, conn);
                    command3.ExecuteNonQuery();

                    string SQLCommand4 = "ALTER TABLE [" + Table + "] ADD  CONSTRAINT [DF_" + Table + "_c_CleanID]  DEFAULT (newid()) FOR [c_CleanID]";
                    SqlCommand command4 = new SqlCommand(SQLCommand4, conn);
                    command4.ExecuteNonQuery();

                   
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }

                }
            }
        }
      

        public static bool CreateDB(string CS = "", string DB = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            

            String str = "IF OBJECT_ID ('"+ DB +"') IS NOT NULL CREATE DATABASE "+ DB ;
            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();          
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {              
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return true;
        }
        public static bool CreateDB_SQLite(string CS = "", string DB = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ADBLight_SQLite_ConnString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (!File.Exists(DB + ".db"))
            {
                System.Data.SQLite.SQLiteConnection.CreateFile(DB + ".db");
            }

            String str = "";
            if (!File.Exists(DB + ".db"))
            {
                str = new SQLiteConnection("Data Source=" + DB + ".db" + ";Version=3;New=True;Compress=True;Password=#Andromeda2014;").ToString();
            }
            else if (File.Exists(DB + ".db"))
            {
                str = new SQLiteConnection("Data Source=" + DB + ".db" + ";Version=3;New=False;Compress=True;Password=#Andromeda2014;").ToString();
            }

            
            SQLiteConnection myConn = new SQLiteConnection(); 
            SQLiteCommand myCommand = new SQLiteCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return true;
        }
        
        public static void TruncateTable(string CS = "", string DB = "", String Table = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String str = String.Format("IF OBJECT_ID ('" + "{0}.dbo.{1}" + "') IS NOT NULL truncate table [{0}].[dbo].[{1}]", DB, Table);
            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myConn.ChangeDatabase(DB);
                myCommand.ExecuteNonQuery();
             
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

        public static void DropTable(string CS = "", string DB = "", String Table = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String str = String.Format("IF OBJECT_ID ('" + "{0}.dbo.{1}" + "') IS NOT NULL drop table [{0}].[dbo].[{1}]", DB, Table);
            SqlConnection myConn = new SqlConnection(CS);       
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myConn.ChangeDatabase(DB);
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

        public static void DropTableSQLite(string CS = "", string DB = "", String Table = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ADBLight_SQLite_ConnString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String str = String.Format("IF OBJECT_ID ('" + "{1}" + "') IS NOT NULL drop table [{1}]", DB, Table);
            SQLiteConnection myConn = new SQLiteConnection(CS);
            SQLiteCommand myCommand = new SQLiteCommand(str, myConn);

            try
            {
                myConn.Open();
                myConn.ChangeDatabase(DB);
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

        public static void DropDB(string CS = "", string DB = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }

            SqlConnection myConn = new SqlConnection(CS);
       
            try
            {
                myConn.Open();
                myConn.ChangeDatabase("master");
                string rollbackCommand = @"ALTER DATABASE ["+ DB + "] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";
                SqlCommand deletecommand = new SqlCommand(rollbackCommand, myConn);
                deletecommand.ExecuteNonQuery();
                deletecommand = new SqlCommand(@"DROP DATABASE [" + DB + "]", myConn);            
                deletecommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

          
        }



        public static void Insert(string CS = "", string DB = "", string Table = "", Dictionary<String, String> data = null)
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String columns = "";
            String values = "";
            Boolean returnCode = true;

            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value.ToString());
            }

            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);

            String str = String.Format("IF OBJECT_ID ('" + "{0}.dbo.{1}" + "') IS NOT NULL insert into {0}.dbo.{1}({2}) values({3});", DB, Table, columns, values);
            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }

     

        public static void Update(string CS = "", string DB = "", string Table = "", Dictionary<String, String> data = null, string where = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String vals = "";
            Boolean returnCode = true;

            if (data.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in data)
                {
                    vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString());
                }
                vals = vals.Substring(0, vals.Length - 1);
            }

            String str = String.Format("update {0} set {1} where {2};", Table, vals, where);
            SqlConnection myConn = new SqlConnection(CS + " Initial Catalog=" + DB + ";");
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
          
        }

        public static void Delete(string CS = "", string DB = "", string Table = "", string where = "")
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String str = String.Format("delete from {0} where {1};", Table, where);
            SqlConnection myConn = new SqlConnection(CS );
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
        }


        public static void CreateTable(string CS = "", string DB = "", string Table = "", Dictionary<String, String> Columns = null, bool ReCreate = false)
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String _Columns = "";
            Boolean returnCode = true;

            if (Columns.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in Columns)
                {
                    if (val.Key.ToString().ToLower().IndexOf("ID_IQ") >= 0)
                    {
                        _Columns += String.Format("{0} {1} IDENTITY(1,1) PRIMARY KEY,", val.Key.ToString(), val.Value.ToString());
                    }
                    else
                    {
                        _Columns += String.Format("{0} {1} null,", val.Key.ToString(), val.Value.ToString());
                    }
                }
                _Columns = _Columns.Substring(0, _Columns.Length - 1);
            }


            String str = "";

            if (ReCreate)
            {
                DropTable(CS, DB, Table);
                str = String.Format("IF OBJECT_ID ('" + "{0}.dbo.{1}" + "') IS NULL create table [{0}].[dbo].[{1}](" + "{2}" + ")", DB, Table, _Columns);
            }
            else
            {
                str = String.Format("IF OBJECT_ID ('" + "{0}.dbo.{1}" + "') IS NULL create table [{0}].[dbo].[{1}](" + "{2}" + ")", DB, Table, _Columns);
            }


            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                if (ex.Message.IndexOf("There is already an object named") >= 0) 
                {
                    if (myConn.State == ConnectionState.Open)
                    {
                        myConn.Close();
                    }
                    CleanBackUp.DropTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table); 
                }
                else
                {
                    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
              
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

        }

        public static void CreateTable_SQLite(string CS = "", string DB = "", string Table = "", Dictionary<String, String> Columns = null, bool ReCreate = false)
        {
            String str = "";
            if (string.IsNullOrEmpty(CS))
            {
                
                if (!File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
                {
                    str = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=True;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
                }
                else if (File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
                {
                    str = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=False;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
                }
                CS = str;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }

            String _Columns = "";
            Boolean returnCode = true;

            if (Columns.Count >= 1)
            {
                foreach (KeyValuePair<String, String> val in Columns)
                {
                    string SQLiteType = "";

                    if (val.Value.ToString().ToLower().IndexOf("int") >= 0)
                    {
                        SQLiteType = "INTEGER";
                    }
                    if (val.Value.ToString().ToLower().IndexOf("varchar") >= 0)
                    {
                        SQLiteType = "Text";
                    }

                    if (val.Key.ToString().ToLower().IndexOf("ID_IQ") >= 0)
                    {
                        _Columns += String.Format("{0} {1}  PRIMARY KEY AUTOINCREMENT,", val.Key.ToString(), SQLiteType);
                    }
                    else
                    {
                        _Columns += String.Format("{0} {1} null,", val.Key.ToString(), SQLiteType);
                    }
                }
                _Columns = _Columns.Substring(0, _Columns.Length - 1);
            }

            if (!File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
            {
                CS = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=True;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
            }
            else if (File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
            {
                CS = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=False;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
            }

         

            if (ReCreate)
            {
                DropTable(CS, DB, Table);
                str = String.Format("create table IF NOT EXISTS [{1}](" + "{2}" + ")", DB, Table, _Columns);
            }
            else
            {
                str = String.Format("create table IF NOT EXISTS [{1}](" + "{2}" + ")", DB, Table, _Columns);
            }


            SQLiteConnection myConn = new SQLiteConnection(CS);
            SQLiteCommand myCommand = new SQLiteCommand(str, myConn);

            try
            {
                myConn.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                if (ex.Message.IndexOf("There is already an object named") >= 0)
                {
                    if (myConn.State == ConnectionState.Open)
                    {
                        myConn.Close();
                    }
                    CleanBackUp.DropTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table);
                }
                else
                {
                    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }

        }


        public static DataTable GetDataTable(string CS = "", string DB = "", string Table = "", string sql = "",DataTable dt = null)
        {
            if (string.IsNullOrEmpty(CS))
            {
                CS = ABDLight.Properties.Settings.Default.ABDLightDBConnectionString;
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }
            if (string.IsNullOrEmpty(sql))
            {
                sql = "select * from [" + DB + "].[dbo].[" + Table + "]";
            }

            if (dt == null)
            {
                dt = new DataTable();
            }else
            {
                dt.Rows.Clear();
            }
 

            SqlConnection myConn = new SqlConnection(CS);
            SqlCommand myCommand = new SqlCommand(sql, myConn);

            try
            {
                myConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(myCommand);//SQL запрос
                da.Fill(dt);               
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return dt;
        }
        public static DataTable GetDataTable_SQLite(string CS = "", string DB = "", string Table = "", string sql = "", DataTable dt = null)
        {
            if (!File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
            {
                CS = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=True;"              ;
            }
            else if (File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
            {
                //CS = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=False;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
            }

            if (string.IsNullOrEmpty(CS))
            {
                if (!File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
                {
                    CS = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=True;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
                }
                else if (File.Exists(ABDLight.Properties.Settings.Default.DB + ".db"))
                {
                    CS = "Data Source=" + ABDLight.Properties.Settings.Default.DB + ".db" + ";Version=3;New=False;Compress=True;Password=" + ABDLight.Properties.Settings.Default.Password + ";";
                }
            }
            if (string.IsNullOrEmpty(DB))
            {
                DB = ABDLight.Properties.Settings.Default.DB;
            }
            if (string.IsNullOrEmpty(Table))
            {
                Table = ABDLight.Properties.Settings.Default.Table;
            }
            if (string.IsNullOrEmpty(sql))
            {
                sql = "select * from [" + Table + "]";
            }

            if (dt == null)
            {
                dt = new DataTable();
            }
            else
            {
                dt.Rows.Clear();
            }


            SQLiteConnection myConn = new SQLiteConnection(CS);
            SQLiteCommand myCommand = new SQLiteCommand(sql, myConn);

            try
            {
                myConn.Open();
                SQLiteDataAdapter da = new SQLiteDataAdapter(myCommand);//SQL запрос
                da.Fill(dt);
            }
            catch (System.Exception ex)
            {
                //MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                {
                    myConn.Close();
                }
            }
            return dt;
        }
        public static void OpenTextFileToDB(string file, DataTable dt, string cPage, string separator = ";", bool HDR = true, bool KOV = false, int NumRows = 0)
        {

            if (File.Exists(file))
            {
                if (separator == "tab") separator = "\t";
                Encoding enc = Encoding.GetEncoding(cPage);

                if (File.Exists(file))
                {
                    if (separator == "tab") separator = "\t";
                   
                    using (StreamReader sr = new StreamReader(file, enc))
                    {

                        string line = "";

                        line = sr.ReadLine();
                        separator = AnalizeDelimiter(line);
                        sr.Close();
                    }
                }


                using (StreamReader sr = new StreamReader(file, enc))
                {
                    dt.Rows.Clear();
                    dt.Columns.Clear();



                    string line = "";
                    int r = 0;
                    int col = 0;
                    int Num = 0;
                    string charKOV = "\\\"";
                    line = sr.ReadLine();

                    //Создание базы
                    bool isDBcreate = CleanBackUp.CreateDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                    if (!isDBcreate)
                    {
                        //Удаление базы
                        CleanBackUp.DropDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                        //Создание базы
                        isDBcreate = CleanBackUp.CreateDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                    }
                    //Создаем переменную в которой будем передавать название колонки и её тип
                    Dictionary<String, String> Columns = new Dictionary<string, string>();
                    //Создаем переменную в которой будем передавать название колонки и её содержимое
                    Dictionary<String, String> data = new Dictionary<string, string>();

                    while (line != null && Num <= NumRows)
                    {
                        if (NumRows != 0)
                        {
                            Num += 1;
                        }

                        line.Trim();
                        //определение кол-ва разделителей
                        MatchCollection match1 = Regex.Matches(line, separator);
                        //Console.WriteLine("{0}", match1.Count);
                        if (KOV)
                        {
                            //определение кол-ва разделителей с учетом ковычек
                            MatchCollection match2 = Regex.Matches(line, '"' + separator + '"');
                            line = Regex.Replace(line, charKOV.ToString(), "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                        }
                        string[] row = line.Split(separator.ToCharArray()[0]);



                        if (col == 0) col = row.Length;
                        else if (row.Length != col)
                        {
                            line = sr.ReadLine();
                            continue;
                        }

                        if (r == 0)
                        {
                            /*
                            DataColumn ID_IQ = new DataColumn();
                            ID_IQ.ColumnName = "ID_IQ";
                            ID_IQ.DataType = System.Type.GetType("System.Int32");
                            dt.Columns.Add(ID_IQ);

                            Columns.Add(ID_IQ + "_" + 0, "Int");

                            ID_IQ.SetOrdinal(0);
                             * */
                            for (int i = 0; i < col; i++)
                            {
                                //KOV = false;
                                if (KOV)
                                {
                                    if (HDR)
                                    {
                                        dt.Columns.Add(row[i].ToString() + "_" + i.ToString(), System.Type.GetType("System.String"));
                                        Columns.Add(row[i].ToString() + "_" + i.ToString(), "varchar(MAX)");
                                    }
                                    else
                                    {
                                        dt.Columns.Add("field_" + i.ToString(), System.Type.GetType("System.String"));
                                        Columns.Add("field_" + i.ToString(), "varchar(MAX)");
                                    }
                                }
                                else
                                {
                                    if (HDR)
                                    {
                                        dt.Columns.Add(row[i].ToString(), System.Type.GetType("System.String"));
                                        Columns.Add(row[i].ToString(), "varchar(MAX)");
                                    }
                                    else
                                    {
                                        dt.Columns.Add("field_" + i.ToString(), System.Type.GetType("System.String"));
                                        Columns.Add("field_" + i.ToString(), "varchar(MAX)");
                                    }
                                }
                            }

                            CleanBackUp.CreateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table, Columns);

                            if (HDR)
                            {
                                line = sr.ReadLine();
                                r++;
                                continue;
                            }


                        }

                        DataRow dr = dt.NewRow();
                        data.Clear();
                        for (int i = 0; i < col; i++)
                        {

                            if (dt.Columns[i].ColumnName == ("ID_IQ") || dt.Columns[i].ColumnName == ("iqParentID"))
                            {
                                Int32 val = 0;
                                Int32.TryParse(r.ToString(), out val);
                                //dr[i] = val;
                                data.Add(dt.Columns[i].ColumnName, r.ToString());
                            }
                            else
                            {
                                dr[i] = row[i].ToString();
                                data.Add(dt.Columns[i].ColumnName, dr[i].ToString().Replace("'", "''"));
                            }
                        }
                        dt.Rows.Add(dr);
                        CleanBackUp.Insert(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table, data);

                        if (r > 1000)
                        {
                            // dt.Rows.Clear();
                            // dt.Columns.Clear();
                        }
                        r++;
                        line = sr.ReadLine();
                    }
                }
            }
            else
            {

                MessageBox.Show("Не выбрано ни одного файла!", "Внимание",
                                                 MessageBoxButtons.OK,
                                                 MessageBoxIcon.Error);
            }

        }//end proc

       

        /// <summary>
        /// Чтение CSV-файла и сохранение записей в таблицу БД
        /// </summary>
        /// <returns>
        /// 0 - всё прошло без ошибок
        /// 1 - не удалось сохранить записи в БД
        /// 2 - ещё что-то не удалось
        /// </returns>
        public static int readCSVandSave2DB(string file, DataTable dt, string cPage, string separator = ";", bool HDR = true, bool KOV = false, int NumRows = 0, bool needCreateDb = false) // в параметры можно вынести размер буфера и путь до файла
        {
            // задаём размер буфера, пусть будет 9000 (строк CSV-файла)
            int csvBufferSize = 9000;
            // указываем путь до CSV-файла
            string path2file = file;
            // сюда он будет читаться
            DataTable csvData = new DataTable();
            Dictionary<String, String> Columns = new Dictionary<string, string>();         

            if (File.Exists(path2file))
            {
                if (separator == "tab") separator = "\t";
                Encoding enc = Encoding.GetEncoding(cPage);
                using (StreamReader sr = new StreamReader(file, enc))
                {
                    string line = "";

                    line = sr.ReadLine();
                    separator = AnalizeDelimiter(line);
                    sr.Close();
                }
            }        

            try // пробуем читать
            {
                // для TextFieldParser надо подключить библиотеку Microsoft.VisualBasic
                // но он вовсе не обязателен, CSV в DataTable можете разбирать как хотите
                Encoding enc = Encoding.GetEncoding(cPage);
                using (TextFieldParser csvReader = new TextFieldParser(path2file, enc))
                {
                    
                    csvReader.SetDelimiters(new string[] { separator });
                  
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    int NumTmp = 0;
                    foreach (string column in colFields) // автобиндинг
                    {
                        // названия колонок в таблице
                        DataColumn datecolumnSer = new DataColumn(column);

                        if (column.IndexOf("isCheck") >=0)
                        {
                            csvData.Columns.Add(datecolumnSer.ColumnName, System.Type.GetType("System.String"));
                            Columns.Add(datecolumnSer.ColumnName, "Bit");
                        }
                        else if (column.IndexOf("isChanget") >=0)
                        {
                            csvData.Columns.Add(datecolumnSer.ColumnName, System.Type.GetType("System.String"));
                            Columns.Add(datecolumnSer.ColumnName, "Bit");
                        }
                        else if (column.IndexOf("isBad") >=0)
                        {
                            csvData.Columns.Add(datecolumnSer.ColumnName, System.Type.GetType("System.String"));
                            Columns.Add(datecolumnSer.ColumnName, "Bit");
                        }
                        else
                        {
                            csvData.Columns.Add(datecolumnSer.ColumnName, System.Type.GetType("System.String"));
                            Columns.Add(datecolumnSer.ColumnName, "varchar(Max)");
                        }
                                               
                   
                  
                    }

                    bool isDBcreate = CleanBackUp.CreateDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                    if (!isDBcreate)
                    {
                        //Удаление базы
                        //DBAct.DropDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString , ABDLight.Properties.Settings.Default.DB);
                        CleanBackUp.DropDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                        //Создание базы
                        isDBcreate = CleanBackUp.CreateDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                        // CleanBackUp.TruncateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.InitialCatalog, ABDLight.Properties.Settings.Default.Table);
                        CleanBackUp.CreateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table, Columns);
                    }
                    else
                    {
                        CleanBackUp.CreateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table, Columns,true);
                    }

                    int buffer_wannabe = 0;
                    int main_buffer = 0;
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        int NumForReplace = 0;
                        /*
                         foreach(string item in fieldData)
                         {
                            
                             string tmpStr = "";
                             tmpStr = item.Replace("'","''");
                             fieldData[NumForReplace] = tmpStr;
                             NumForReplace++;
                         }
                    */
                        csvData.Rows.Add(fieldData);

                        buffer_wannabe++;
                        main_buffer++;
                        // начитали 9000 строк, заносим их в базу и сбрасываем буфер на 0
                        if (buffer_wannabe == csvBufferSize)
                        {
                            buffer_wannabe = 0;

                            // функция вставки записей описана ниже
                            if (!insertCurrentBunchOfRecs(csvData, ABDLight.Properties.Settings.Default.Table)) return 1;

                            csvData.Rows.Clear();
                        }
                        if (main_buffer == NumRows)
                        {

                            if (!insertCurrentBunchOfRecs(csvData, ABDLight.Properties.Settings.Default.Table)) return 1;

                            csvData.Rows.Clear();
                            return 0;
                        }
                       
                    }
                    // в последнем чтении в буфере меньше 9000 записей, их тоже надо занести
                    if (buffer_wannabe != 0)
                    {
                        if (!insertCurrentBunchOfRecs(csvData)) return 1;
                        csvData.Rows.Clear();
                  
                    }
                }
            }
            catch { return 2; }

            // всё огонь
            return 0;
        }

        /// <summary>
        /// Сохранение новых записей во временную таблицу в БД
        /// </summary>
        /// <param name="csvData">строки из CSV</param>
        /// <returns></returns>
        public static  bool insertCurrentBunchOfRecs(DataTable csvData, string _DestinationTableName = "",string LoadType = "file", bool RecreateDB = false)
        {
            Dictionary<String, String> Columns = new Dictionary<string, string>();

            if (string.IsNullOrEmpty(_DestinationTableName))
            {
                _DestinationTableName = ABDLight.Properties.Settings.Default.Table;
            }

            if (RecreateDB)
            {
                bool isDBcreate = CleanBackUp.CreateDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                if (!isDBcreate)
                {
                    //Удаление базы
                    //DBAct.DropDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString + " Initial Catalog=" + ABDLight.Properties.Settings.Default.InitialCatalog + ";", ABDLight.Properties.Settings.Default.InitialCatalog);
                    CleanBackUp.DropDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                    //Создание базы
                    isDBcreate = CleanBackUp.CreateDB(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB);
                    foreach (DataColumn column in csvData.Columns)
                    {
                        /*
                           if (Grid.Columns["isCheck"] != null) Grid.Columns["isCheck"].DefaultHeaderCellType = System.Type.GetType("System.Bool");
                           if (Grid.Columns["isChanget"] != null) Grid.Columns["isChanget"].ReadOnly = true;
                           if (Grid.Columns["isBad"] != null) Grid.Columns["isBad"].ReadOnly = true;
                        */
                        
                        if (column.ColumnName.IndexOf("isCheck") >= 0) Columns.Add(column.ColumnName, "Bit");
                        else if (column.ColumnName.IndexOf("isChanget") >= 0) Columns.Add(column.ColumnName, "Bit");
                        else if (column.ColumnName.IndexOf("isBad") >= 0) Columns.Add(column.ColumnName, "Bit");
                        else if (column.ColumnName.IndexOf("ID_IQ") >= 0) Columns.Add(column.ColumnName, "int");
                        else Columns.Add(column.ColumnName, "varchar(Max)");
                    }
                    CleanBackUp.CreateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, _DestinationTableName, Columns);
                }

            }
            else if (!RecreateDB)
            {

                 
                
                    try
                    {
                        //DBAct.DropTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, ABDLight.Properties.Settings.Default.Table);
                        
                        foreach (DataColumn column in csvData.Columns)
                        {
                            
                            if (column.ColumnName.IndexOf("isCheck") >= 0) Columns.Add(column.ColumnName, "Bit");
                            else if (column.ColumnName.IndexOf("isChanget") >= 0) Columns.Add(column.ColumnName, "Bit");
                            else if (column.ColumnName.IndexOf("isBad") >= 0) Columns.Add(column.ColumnName, "Bit");
                            else if (column.ColumnName.IndexOf("ID_IQ") >= 0) Columns.Add(column.ColumnName, "int");
                            else Columns.Add(column.ColumnName, "varchar(Max)");
                        }

                        CleanBackUp.CreateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, _DestinationTableName, Columns,true);

                    }
                    catch (System.Exception ex)
                    {

                        CleanBackUp.CreateTable(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString, ABDLight.Properties.Settings.Default.DB, _DestinationTableName, Columns,true);
                    }
                    
  
            }

           
            if (string.IsNullOrEmpty(LoadType) || LoadType.ToLower() == "file")
            {
                try
                {
                    using (SqlConnection dbConnection = new SqlConnection(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString))
                    {
                        dbConnection.Open();
                        dbConnection.ChangeDatabase(ABDLight.Properties.Settings.Default.DB);
                        // вся соль вот в этом классе - SqlBulkCopy - он делает всю магию
                        using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                        {
                            // если таблица в какой-то схеме, то указать это
                            //s.DestinationTableName = "ИМЯСХЕМЫ.ИМЯТАБЛИЦЫ";
                            s.DestinationTableName = _DestinationTableName;
                            foreach (var column in csvData.Columns)
                            {
                                s.ColumnMappings.Add(column.ToString(), column.ToString());
                            }
                            s.WriteToServer(csvData);
                        }
                    }
                }
                catch { return false; }
                finally { }
            }else
            if (LoadType.ToLower() == "db")
            {

                try
                {
                    using (SqlConnection dbConnection = new SqlConnection(ABDLight.Properties.Settings.Default.ABDLightDBConnectionString))
                    {
                        dbConnection.Open();
                        dbConnection.ChangeDatabase(ABDLight.Properties.Settings.Default.DB);
                        // вся соль вот в этом классе - SqlBulkCopy - он делает всю магию
                        using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                        {
                            // если таблица в какой-то схеме, то указать это
                            //s.DestinationTableName = "ИМЯСХЕМЫ.ИМЯТАБЛИЦЫ";
                            s.DestinationTableName = _DestinationTableName;
                            foreach (var column in csvData.Columns)
                            {                               
                                s.ColumnMappings.Add(column.ToString(), column.ToString());
                            }
                            s.WriteToServer(csvData);
                        }
                    }
                }
                catch { return false; }
                finally { }
            }
            return true;
        }



        private static string getOutput(string strInput)
        {
            var regex = new Regex("(.)\\1+");
            strInput = regex.Replace(strInput, "$1"); // remove duplicate characters
            strInput = removeOwels(strInput);
            return (strInput + "&&&&&").ToString().Substring(0, 5);
        }

        private static string removeOwels(string strInput)
        {
            string strOutput = "";
            if (strInput.Length <= 5)
                strOutput = strInput;
            else
            {
                string ProcessedString = strInput.Substring(0, 1);
                for (int vLoop = 1; vLoop < strInput.Length; vLoop++)
                {
                    string NextChar = strInput.Substring(vLoop, 1);
                    if (!((NextChar == "A" || NextChar == "E" || NextChar == "I" || NextChar == "O" || NextChar == "U") && ProcessedString.Length + strInput.Length - vLoop > 5))
                        ProcessedString += NextChar;

                }
                strOutput = ProcessedString;
            }
            return strOutput;
        }


        public static string AnalizeDelimiter(string line)
        {
            string[] delimiters = { ";", ":", ",","\t"," " };
               

            int charDelimiterCount = 0;

            char[] chars = line.ToCharArray();

            int DelimiterNum = 0;
            string DetectedDelimiter = ",";
            foreach (string delimiter in delimiters)
            {
                //определение кол-ва разделителей
                MatchCollection match = Regex.Matches(line, delimiter);
                if(match.Count > DelimiterNum)
                {
                    DelimiterNum = match.Count;
                    DetectedDelimiter = delimiter;
                }
            }

            return DetectedDelimiter;
            
        }
    }
}
/*
                            CleanBackUpDB.iqPostCode = item.post_code;
                            CleanBackUpDB.iqFullAddress = item.full_name;
                            CleanBackUpDB.iqHouse = item.house;
                            CleanBackUpDB.iqCase = item.Case;
                            CleanBackUpDB.iqBuild = item.build;
                            CleanBackUpDB.iqLiter = item.liter;
                            CleanBackUpDB.iqVlad = item.vlad;
                            CleanBackUpDB.iqBlock = item.block;
                            CleanBackUpDB.iqPom = item.pom;
                            CleanBackUpDB.iqPav = item.pav;
                            CleanBackUpDB.iqUch = item.uch;
                            CleanBackUpDB.iqObsch = item.obsch;
                            CleanBackUpDB.iqFlat = item.flat;
                            CleanBackUpDB.iqOffice = item.office;
                            CleanBackUpDB.iqKom = item.kom;
                            CleanBackUpDB.iqKab = item.kab;
                            CleanBackUpDB.iqAbon = item.abon;
                            CleanBackUpDB.iqVch = item.vch;
                            CleanBackUpDB.iqKLADR = item.KLADR;
                            CleanBackUpDB.iqError = item.ErrorCode;
                            CleanBackUpDB.iqStatus = item.status.Replace(";", ",");
                            CleanBackUpDB.iqCountry = cmbCountry.Items[((frmMine)this.Owner).cfg.defCountry].ToString();
                            CleanBackUpDB.iqRegion = item.region;
                            CleanBackUpDB.iqRegionType = item.region_type;
                            CleanBackUpDB.iqSubRegion = item.subregion;
                            CleanBackUpDB.iqSubRegionType = "";
                            CleanBackUpDB.iqCity = item.city;
                            CleanBackUpDB.iqCityType = item.city_type;
                            CleanBackUpDB.iqNp = item.np;
                            CleanBackUpDB.iqNpType = item.np_type;
                            CleanBackUpDB.iqStreet = item.street;
                            CleanBackUpDB.iqStreetType = item.street_type;
                            CleanBackUpDB.Login = Properties.Settings.Default.Login;
                            CleanBackUpDB.FileName = Properties.Settings.Default.CleanFileName;

*/

/*

if (cCfg.FuncType == "add")
                            {
                                // Добавление в бызу построчно
                                // CleanBackUpDB.AddCleanRow(CleanBackUpDB);
                                //////////////////////////////

                               
                                using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ABDLightDBConnectionString))
                                {
                                    conn.Open();
                                    try
                                    {
                                        string SQLCommand = "INSERT INTO CleanBackUp" +

                                        "([c_Login]" +
                                        ",[c_FileName]" +
                                        ",[c_iqPostCode]" +
                                        ",[c_iqFullAddress]" +
                                        ",[c_iqHouse]" +
                                        ",[c_iqCase]" +
                                        ",[c_iqBuild]" +
                                        ",[c_iqLiter]" +
                                        ",[c_iqVlad]" +
                                        ",[c_iqBlock]" +
                                        ",[c_iqPom]" +
                                        ",[c_iqPav]" +
                                        ",[c_iqUch]" +
                                        ",[c_iqObsch]" +
                                        ",[c_iqFlat]" +
                                        ",[c_iqKom]" +
                                        ",[c_iqOffice]" +
                                        ",[c_iqKab]" +
                                        ",[c_iqAbon]" +
                                        ",[c_iqVch]" +
                                        ",[c_iqKLADR]" +
                                        ",[c_iqError]" +
                                        ",[c_iqStatus]" +
                                        ",[c_iqCountry]" +
                                        ",[c_iqRegion]" +
                                        ",[c_iqRegionType]" +
                                        ",[c_iqSubRegion]" +
                                        ",[c_iqSubRegionType]" +
                                        ",[c_iqCity]" +
                                        ",[c_iqCityType]" +
                                        ",[c_iqNp]" +
                                        ",[c_iqNpType]" +
                                        ",[c_iqStreet]" +
                                        ",[c_iqStreetType]" +
                                        ",[c_iqWorkAddress]" +
                                        ",[c_ISOK])" +
                                    "VALUES" +

                                        "('" + ABDLight.Properties.Settings.Default.Login + "'," +
                                        "'" + cCfg.LastFileName + "'," +
                                        "'" + item.post_code + "'," +
                                        "'" + item.full_name + "'," +
                                        "'" + item.house + "'," +
                                        "'" + item.Case + "'," +
                                        "'" + item.build + "'," +
                                        "'" + item.liter + "'," +
                                        "'" + item.vlad + "'," +
                                        "'" + item.block + "'," +
                                        "'" + item.pom + "'," +
                                        "'" + item.pav + "'," +
                                        "'" + item.uch + "'," +
                                        "'" + item.obsch + "'," +
                                        "'" + item.flat + "'," +
                                        "'" + item.kom + "'," +
                                        "'" + item.office + "'," +
                                        "'" + item.kab + "'," +
                                        "'" + item.abon + "'," +
                                        "'" + item.vch + "'," +
                                        "'" + item.KLADR + "'," +
                                        "'" + item.ErrorCode + "'," +
                                        "'" + item.status + "'," +
                                        "'" + cmbCountry.Items[((frmMine)this.Owner).cfg.defCountry].ToString() + "'," +
                                        "'" + item.region + "'," +
                                        "'" + item.region_type + "'," +
                                        "'" + item.subregion + "'," +
                                        "'" + "" + "'," +
                                        "'" + item.city + "'," +
                                        "'" + item.city_type + "'," +
                                        "'" + item.np + "'," +
                                        "'" + item.np_type + "'," +
                                        "'" + item.street + "'," +
                                        "'" + item.street_type + "'," +
                                        "'" + AddrString.Row[numRow].ToString() +"'," +
                                        "'" + Address.ISOK + "')";

                                        SqlCommand command = new SqlCommand(SQLCommand, conn);
                                        command.ExecuteNonQuery();
                                    }

                                    finally
                                    {
                                        // Always call Close when done reading.

                                    }
                                }
                                
                            }

                            if (cCfg.FuncType == "update")
                            {
                                // ((frmMine)this.Owner).dtMain.Rows[cCfg.StartRow].SetField("isChanget", true);
                                // Обновление записи (строки) по c_RowGUID 
                                // CleanBackUpDB.UpdateCleanRow(CleanBackUpDB);
                                //////////////////////////////////////////////


                                  
                                using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ABDLightDBConnectionString))
                                {
                                    conn.Open();
                                    try
                                    {
                               
                                        string SQLCommand = "update dbo.CleanBackUp set" +
                                        "[c_Login]=" + "'" + ABDLight.Properties.Settings.Default.Login + "'," +
                                        "[c_FileName]=" + "'" + cCfg.LastFileName + "'," +
                                        "[c_iqPostCode]=" + "'" + item.post_code + "'," +
                                        "[c_iqFullAddress]=" + "'" + item.full_name + "'" +
                                         " where c_RowGUID=" + Address.GUID;
                                        SqlCommand command = new SqlCommand(SQLCommand, conn);
                                        command.ExecuteNonQuery();

                                        string SQLCommand2 = "update dbo.CleanBackUp set" +
                                        "[c_iqHouse]=" + "'" + item.house + "'," +
                                        "[c_iqCase]=" + "'" + item.Case + "'," +
                                        "[c_iqBuild]=" + "'" + item.build + "'," +
                                        "[c_iqLiter]=" + "'" + item.liter + "'," +
                                        "[c_iqVlad]=" + "'" + item.vlad + "'," +
                                        "[c_iqBlock]=" + "'" + item.block + "'," +
                                        "[c_iqPom]=" + "'" + item.pom + "'," +
                                        "[c_iqPav]=" + "'" + item.pav + "'," +
                                        "[c_iqUch]=" + "'" + item.uch + "'," +
                                        "[c_iqObsch]=" + "'" + item.obsch + "'," +
                                        "[c_iqFlat]=" + "'" + item.flat + "'," +
                                        "[c_iqKom]=" + "'" + item.kom + "'," +
                                        "[c_iqOffice]=" + "'" + item.office + "'," +
                                        "[c_iqKab]=" + "'" + item.kab + "'," +
                                        "[c_iqAbon]=" + "'" + item.abon + "'," +
                                        "[c_iqVch]=" + "'" + item.abon + "'," +
                                        "[c_iqKLADR]=" + "'" + item.KLADR + "'," +
                                        "[c_iqError]=" + "'" + item.ErrorCode + "'," +
                                        "[c_iqStatus]=" + "'" + item.status + "'" +
                                        " where c_RowGUID=" + Address.GUID;
                                        SqlCommand command2 = new SqlCommand(SQLCommand2, conn);
                                        command2.ExecuteNonQuery();

                                        string SQLCommand3 = "update dbo.CleanBackUp set" +
                                        "[c_iqCountry]=" + "'" + cmbCountry.Items[((frmMine)this.Owner).cfg.defCountry].ToString() + "'," +
                                        "[c_iqRegion]=" + "'" + item.region + "'," +
                                        "[c_iqRegionType]=" + "'" + item.region_type + "'," +
                                        "[c_iqSubRegion]=" + "'" + item.subregion + "'," +                         
                                        "[c_iqCity]=" + "'" + item.city + "'," +
                                        "[c_iqCityType]=" + "'" + item.city_type + "'," +
                                        "[c_iqNp]=" + "'" + item.np + "'," +
                                        "[c_iqNpType]=" + "'" + item.np_type + "'," +
                                        "[c_iqStreet]=" + "'" + item.street + "'," +
                                        "[c_iqStreetType]=" + "'" + item.street_type + "'," +
                                        "[c_iqWorkAddress]=" + "'" + ((frmMine)this.Owner).dtMain.Rows[cCfg.StartRow].ToString() + "'," +
                                        "[c_ISOK]=" + "'" + Address.ISOK + "'" +
                                       " where c_RowGUID=" + Address.GUID;
                                        SqlCommand command3 = new SqlCommand(SQLCommand3, conn);
                                         command3.ExecuteNonQuery();
                                    }

                                    finally
                                    {
                                        // Always call Close when done reading.

                                    }
                                }                               
                            }
*/